Basics 基础

A new file can be created using thecreatemethod with the filename as first parameter.

创建一个文件，使用第一个参数作为文件名。

Excel::create('Filename');

To manipulate the creation of the file you can use the callback

可以使用回调函数操作创建的文件。

Excel::create('Filename', function($excel) {

// Call writer methods here

});

Changing properties

更改属性

There are a couple of properties we can change inside the closure. Most of them are set to the config values by default. See  app/config/packages/maatwebsite/excel/config.php.

有几个属性可以改变    大多数设置了默认。

Excel::create('Filename', function($excel) {

 // Set the title  设置标题

                 $excel->setTitle('Our new awesome title');

// Chain the setters  设置创作人

                    $excel->setCreator('Maatwebsite')

                              ->setCompany('Maatwebsite');

// Call them separately   设置 介绍说明

                     $excel->setDescription('A demonstration to change the file properties');

});

Go to the reference guide to see a list of available properties.

去参考指南以查看可用属性的列表。

Exporting导出

To download the created file, use->export($ext) or ->download($ext).

下载创建的excel

Export to Excel5 (xls)格式

Excel::create('Filename', function($excel) {

})->export('xls');

// or

->download('xls');

Export to Excel2007 (xlsx)格式

->export('xlsx');

// or

->download('xlsx');

Export to CSV格式

->export('csv');

// or

->download('csv');

You can set the default enclosure and delimiter inside the config

在配置文件里设置 范围 和 定界符

Export to PDF 导出为PDF格式

To export files to pdf, you will have to include"dompdf/dompdf": "~0.6.1","mpdf/mpdf": "~5.7.3"or"tecnick.com/tcpdf": "~6.0.0"in yourcomposer.jsonand change theexport.pdf.driverconfig setting accordingly.

->export('pdf');

如果想要导出格式为PDF，则需要安装扩展

NewExcelFile injections

新的Excel文件 注入

Following the Laravel 5.0 philosophy with its new awesome FormRequest injections, we introduce you NewExcelFile injections.

NewExcelFile class

This NewExcelFile is a wrapper for a new Excel file. Inside thegetFilename()you can declare the wanted filename.

class UserListExport extends \Maatwebsite\Excel\Files\NewExcelFile {

public function getFilename()

        {

           return 'filename';

          }

}

Usage  使用 用途

You can inject these NewExcelFiles inside the __constructor or inside the method (when using Laravel 5.0), in e.g. the controller.

class ExampleController extends Controller {

public function exportUserList(UserListExport $export)

{

// work on the export

return $export->sheet('sheetName', function($sheet)

{

})->export('xls');

}

}

Export Handlers

导出事件处理器

To decouple your Excel-export code completely from the controller, you can use the export handlers.

class ExampleController extends Controller {

public function exportUserList(UserListExport $export)

{

// Handle the export

$export->handleExport();

}

}

ThehandleExport()method will dynamically call a handler class which is your class name appended withHandler

class UserListExportHandler implements \Maatwebsite\Excel\Files\ExportHandler {

public function handle(UserListExport $export)

{

// work on the export

return $export->sheet('sheetName', function($sheet)

{

})->export('xls');

}

}

Store on server   把文件保存到服务器上

To store the created file on the server, use->store($ext, $path = false, $returnInfo = false)or->save().

Normal export to default storage path

By default the file will be stored inside theapp/storage/exportsfolder, which has been defined in theexport.phpconfig file.

Excel::create('Filename', function($excel) {

// Set sheets

})->store('xls');

Normal export to custom storage path   一般导出到自定义的路径

If you want to use a custom storage path (e.g. to separate the files per client), you can set the folder as the second parameter.

->store('xls', storage_path('excel/exports'));

Store and export  保存并导出

->store('xls')->export('xls');

Store and return storage info  保存并显示保存信息

If you want to return storage information, set the third paramter to true or change the config setting insideexport.php.

->store('xls', false, true);

KeyExplanation

fullFull path with filename

pathPath without filename

fileFilename

titleFile title

extFile extension

Make sure your storage folder iswritable!

Sheets  工作表

Creating a sheet   创建一个工作表

To create a new sheet inside our newly created file, use->sheet('Sheetname').

Excel::create('Filename', function($excel) {

$excel->sheet('Sheetname', function($sheet) {

// Sheet manipulation

});

})->export('xls');

Creating multiple sheets   创建多个工作表

You can set as many sheets as you like inside the file:

Excel::create('Filename', function($excel) {

// Our first sheet

$excel->sheet('First sheet', function($sheet) {

});

// Our second sheet

$excel->sheet('Second sheet', function($sheet) {

});

})->export('xls');

Changing properties   修改属性

There are a couple of properties we can change inside the closure. Most of them are set to the config values by default. Seeapp/config/packages/maatwebsite/excel/config.php.

Excel::create('Filename', function($excel) {

$excel->sheet('Sheetname', function($sheet) {

$sheet->setOrientation('landscape');

});

})->export('xls');

Go to the reference guide to see a list of available properties.

Default page margin    页边距  页边空白

It's possible to set the default page margin inside the config fileexcel::export.sheets. It accepts boolean, single value or array.

To manually set the page margin you can use:->setPageMargin()

// Set top, right, bottom, left

$sheet->setPageMargin(array(

0.25, 0.30, 0.25, 0.30

));

// Set all margins

$sheet->setPageMargin(0.25);

Password protecting a sheet   使用密码保护一个工作表

A sheet can be password protected with$sheet->protect():

// Default protect   默认设置

                 $sheet->protect('password');

// Advanced protect       更高级的设置

$sheet->protect('password', function(\PHPExcel_Worksheet_Protection $protection) {

              $protection->setSort(true);

});

Creating a sheet from an array      用一个数组创建一个工作表

Array

To create a new file from an array use->fromArray($source, $nullValue, $startCell, $strictNullComparison, $headingGeneration)inside the sheet closure.

Excel::create('Filename', function($excel) {  

          $excel->sheet('Sheetname', function($sheet) {

                        $sheet->fromArray(array(   使用fromArray()

                                      array('data1', 'data2'),

                                      array('data3', 'data4')

                        ));

           });

})->export('xls');

Alternatively you can use->with().  或者使用 with()

$sheet->with(array(

                array('data1', 'data2'),

                array('data3', 'data4')

));

If you want to pass variables inside the closure, useuse($data)

$data = array(

             array('data1', 'data2'),

             array('data3', 'data4')

);

Excel::create('Filename', function($excel) use($data) {

                    $excel->sheet('Sheetname', function($sheet) use($data) {

                    $sheet->fromArray($data);

         });

})->export('xls');

Null comparision

By default 0 is shown as an empty cell. If you want to change this behaviour, you can pass true as 4th parameter:

// Will show 0 as 0

$sheet->fromArray($data, null, 'A1', true);

To change the default behaviour, you can useexcel::export.sheets.strictNullComparisonconfig setting.

Eloquent model

It's also possible to pass an Eloquent model and export it by using->fromModel($model). The method accepts the same parameters as fromArray

Auto heading generation

By default the export will use the keys of your array (or model attribute names) as first row (header column). To change this behaviour you can edit the default config setting (excel::export.generate_heading_by_indices) or passfalseas 5th parameter:

// Won't auto generate heading columns

$sheet->fromArray($data, null, 'A1', false, false);

Row manipulation   一行发操作

Manipulate certain row   操作某一行

Change cell values

// Manipulate first row           设置第一行

$sheet->row(1, array(

'test1', 'test2'

));

// Manipulate 2nd row            设置第二行

$sheet->row(2, array(

'test3', 'test4'

));

Manipulate row cells      操作行单元格

// Set black background         设置黑色背景

$sheet->row(1, function($row) {

               // call cell manipulation methods

               $row->setBackground('#000000');

});

Append row  附加行

// Append row after row 2   第二行后添加 附加行

$sheet->appendRow(2, array(

              'appended', 'appended'

));

// Append row as very last   最后添加附加行

$sheet->appendRow(array(

              'appended', 'appended'

));

Prepend row   前置行

// Add before first row

$sheet->prependRow(1, array(

               'prepended', 'prepended'

));

// Add as very first

$sheet->prependRow(array(

                   'prepended', 'prepended'

));

Append multiple rows    附加多行

// Append multiple rows

$sheet->rows(array(

              array('test1', 'test2'),

              array('test3', 'test4')

));

// Append multiple rows

$sheet->rows(array(

                array('test5', 'test6'),

                array('test7', 'test8')

));

Cell manipulation  单元格操作

$sheet->cell('A1', function($cell) {

// manipulate the cell

});

$sheet->cells('A1:A5', function($cells) {

// manipulate the range of cells

});

Set background

To change the background of a range of cells we can use->setBackground($color, $type, $colorType)

// Set black background

$cells->setBackground('#000000');

Change fonts

// Set with font color

$cells->setFontColor('#ffffff');

// Set font family

$cells->setFontFamily('Calibri');

// Set font size

$cells->setFontSize(16);

// Set font weight to bold

$cells->setFontWeight('bold');

// Set font

$cells->setFont(array(

'family'    => 'Calibri',

'size'      => '16',

'bold'      =>  true

));

Set borders

// Set all borders (top, right, bottom, left)

$cells->setBorder('solid', 'none', 'none', 'solid');

// Set borders with array

$cells->setBorder(array(

'borders' => array(

'top'  => array(

'style' => 'solid'

),

)

));

Set horizontal alignment    设置水平对齐方式

// Set alignment to center

$cells->setAlignment('center');

Set vertical alignment    设置垂直对其方式

// Set vertical alignment to middle

$cells->setValignment('middle');

Sheet styling   工作表样式

General styling

If you want to change the general styling of your sheet (not cell or range specific), you can use the->setStyle()method.

// Set font with ->setStyle()`

$sheet->setStyle(array(

             'font' => array(

                           'name'      =>  'Calibri',

                            'size'      =>  15,

                            'bold'      =>  true

               )

));

Fonts

To change the font for the current sheet use->setFont($array):

$sheet->setFont(array(

'family'    => 'Calibri',

'size'      => '15',

'bold'      => true

));

Separate setters

// Font family

$sheet->setFontFamily('Comic Sans MS');

// Font size

$sheet->setFontSize(15);

// Font bold

$sheet->setFontBold(true);

Borders

You can set borders for the sheet, by using:

// Sets all borders

$sheet->setAllBorders('thin');

// Set border for cells

$sheet->setBorder('A1', 'thin');

// Set border for range

$sheet->setBorder('A1:F10', 'thin');

Go to the reference guide to see a list of available border styles

Freeze rows

If you want to freeze a cell, row or column, use:

// Freeze first row

$sheet->freezeFirstRow();

// Freeze the first column

$sheet->freezeFirstColumn();

// Freeze the first row and column

$sheet->freezeFirstRowAndColumn();

// Set freeze

$sheet->setFreeze('A2');

Auto filter

To enable the auto filter use->setAutoFilter($range = false).

// Auto filter for entire sheet

$sheet->setAutoFilter();

// Set auto filter for a range

$sheet->setAutoFilter('A1:E10');

Cell size

Set column width

To set the column width use->setWidth($cell, $width).

// Set width for a single column

$sheet->setWidth('A', 5);

// Set width for multiple cells

$sheet->setWidth(array(

'A'    =>  5,

'B'    =>  10

));

Set row height

To set the row height use->setHeight($row, $height).

// Set height for a single row

$sheet->setHeight(1, 50);

// Set height for multiple rows

$sheet->setHeight(array(

1    =>  50,

2    =>  25

));

Set cell size

To set the cell size use->setSize($cell, $width, $height).

// Set size for a single cell

$sheet->setSize('A1', 500, 50);

$sheet->setSize(array(

'A1' => array(

'width'    => 50

'height'    => 500,

)

));

Auto size

By default the exported file be automatically auto sized. To change this behaviour you can either change the config or use the setters:

// Set auto size for sheet

$sheet->setAutoSize(true);

// Disable auto size for sheet

$sheet->setAutoSize(false);

// Disable auto size for columns

$sheet->setAutoSize(array(

'A', 'C'

));

The default config setting can be found in:export.php.

Column merging

Merging cells

To merge a range of cells, use->mergeCells($range).

$sheet->mergeCells('A1:E1');

Merging columns and rows

To merge columns and rows, use->setMergeColumn($array).

$sheet->setMergeColumn(array(

'columns' => array('A','B','C','D'),

'rows' => array(

array(2,3),

array(5,11),

)

));

Column formatting

To tell Excel how it should interpret certain columns, you can use->setColumnFormat($array).

// Format column as percentage

$sheet->setColumnFormat(array(

'C' => '0%'

));

// Format a range with e.g. leading zeros

$sheet->setColumnFormat(array(

'A2:K2' => '0000'

));

// Set multiple column formats

$sheet->setColumnFormat(array(

'B' => '0',

'D' => '0.00',

'F' => '@',

'F' => 'yyyy-mm-dd',

));

Go to the reference guide to see a list of available formats.

Calling PHPExcel's native methods

It's possible to call all native PHPExcel methods on the$exceland$sheetobjects.

Calling Workbook methods

Example:

// Get default style for this workbook

$excel->getDefaultStyle();

Calling worksheet methods

Example:

// Protect cells

$sheet->protectCells('A1', $password);

Head over to PHPOffice to learn more about the native methods.